<?php

class faculty_Model_DbTable_DbFaculty extends Zend_Db_Table_Abstract
{
	protected $_name ="tbwu_faculty";
	
	public function getAllBranch(){
		$db = $this->getAdapter();
		$sql = "SELECT b.`branch_id`,b.`branch_name` FROM `tbwu_branch` AS b WHERE b.`status`=1";
		return $db->fetchAll($sql);
	}
	public function getAllFaculty(){
		$db = $this->getAdapter();
		$session_user=new Zend_Session_Namespace('auth');
		$branch_id = $session_user->branch;
		$user_type_id = $session_user->level;
		$where = "";
		if($user_type_id==1){
			$where = "";
		}else {
			$where="and f.`branch_id`=$branch_id";
		}
		$sql = "SELECT 
				  f.`faculty_id`,
				  f.`faculty_name`,
				  b.`branch_name` ,
				  f.`status`,
  				  f.`description` 
				FROM
				  `tbwu_faculty` AS f,
				  `tbwu_branch` AS b
				WHERE f.`branch_id` = b.`branch_id` $where
				  ORDER BY f.`faculty_id`";
		return $db->fetchAll($sql);
	}
	public function getFacultyById($id){
		$db = $this->getAdapter();
		$sql="SELECT 
				  f.`faculty_id`,
				  f.`faculty_name`,
				  f.`status`,
				  f.`description`,
				  b.`branch_id` 
				FROM
				  `tbwu_faculty` AS f,
				  `tbwu_branch` AS b
				WHERE f.`branch_id` = b.`branch_id` 
				  AND f.`faculty_id` = $id";
		return $db->fetchRow($sql);
	}
	public function getBranchByFacID($id){
		$db = $this->getAdapter();
		try {
			$sql="SELECT 
					  bf.`branch_id`,
					  b.`branch_name` 
					FROM
					  `tbwu_branch_faculty` AS bf,
					  `tbwu_branch` AS b 
					WHERE bf.`branch_id` = b.`branch_id` 
					  AND bf.`faculty_id` = $id";
			return $db->fetchAll($sql);
		}catch (Exception $e){
			echo $e->getMessage();exit();
		}
		
	}
	public function getBranchNotInBF($id){
		$db = $this->getAdapter();
		$sql = "SELECT 
				  b.`branch_id`,
				  b.`branch_name`
				FROM
				  `tbwu_branch` AS b
				WHERE b.`branch_id` NOT IN(SELECT bf.`branch_id` FROM 
				`tbwu_branch_faculty` AS bf WHERE bf.`faculty_id`=$id )";
		return $db->fetchAll($sql);
	}
    public function addFaculty($data)
    {
    	$db= $this->getAdapter();
    	$db->beginTransaction();
    	$session_user=new Zend_Session_Namespace('auth');
    	$branch_id = $session_user->branch;
    	$user_id = $session_user->user_id;
    	$user_type = $session_user->level;
    	if($user_type==1){
    		$branch=$data["branch"];
    	}else {
    		$branch = $branch_id;
    	}
    	
    	//$branch = $data["branch_select"];
    	try {
	    	$arrFaculty = array(
	    		'branch_id'			=>	$branch,
	    		'faculty_name'		=>	$data["faculty"],
	    		'description'		=>	$data["description"],
	    		'created_date'		=>	new Zend_Date(),
	    		'user_id'			=>	$user_id,
	    		'status'			=>	$data["status"]
	    	);
	    	$faculty = $this->insert($arrFaculty);
// 	    	for($i=0;$i<count($branch);$i++){
// 	    		$branchFac = array(
// 	    				'branch_id'		=>	$branch[$i],
// 	    				'faculty_id'	=>	$faculty
// 	    		);
// 	    		$this->_name = "tbwu_branch_faculty";
// 	    		$this->insert($branchFac);
// 	    	}
	    	
	    $db->commit();
    	}catch (Exception $e){
    		$db->rollBack();
    		echo $e->getMessage();
    		exit();
    	}
    }
    public function editFuculty($data){
    	$db = $this->getAdapter();
    	$db->beginTransaction();
    	$id = $data["id"];
    	$session_user=new Zend_Session_Namespace('auth');
    	$branch_id = $session_user->branch;
    	$user_id = $session_user->user_id;
    	$user_type = $session_user->level;
    	if($user_type==1){
    		$branch=$data["branch"];
    	}else {
    		$branch = $branch_id;
    	}
    	try {
    		$arrFaculty = array(
    				'branch_id'			=>	$branch,
    				'faculty_name'		=>	$data["faculty"],
    				'description'		=>	$data["description"],
    				'created_date'		=>	new Zend_Date(),
    				'user_id'			=>	$user_id,
    				'status'			=>	$data["status"]
    		);
    		$where = $db->quoteInto('faculty_id=?', $id);
    		$this->update($arrFaculty, $where);
    		
//     		$sql = "DELETE FROM tbwu_branch_faculty WHERE faculty_id = $id";
//     		$db->query($sql);
    		
//     		for($i=0;$i<count($branch);$i++){
//     			$branchFac = array(
//     				'branch_id'		=>	$branch[$i],
//     				'faculty_id'	=>	$id
//     			);
//     			$this->_name = "tbwu_branch_faculty";
//     			$this->insert($branchFac);
//     		}
	    	$db->commit();
    	}catch (Exception $e){
    		$db->rollBack();
    		echo $e->getMessage();
    		exit();
    	}
    }
    public function deleteFaculty($id){
    	$db = $this->getAdapter();
    	$sql = "DELETE FROM tbwu_branch_faculty WHERE faculty_id = $id";
    	$db->query($sql);
    }
}

